<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);

define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';


// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();


require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';


// Init excel
$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:I1');
$activeSheet->setCellValue('A1', 'Member statistic On Job');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 5;
$activeSheet->setCellValue('A'.$index, 'Member ID');
$activeSheet->setCellValue('B'.$index, 'Name');
$activeSheet->setCellValue('C'.$index, 'Phone');
$activeSheet->setCellValue('D'.$index, 'Email');
$activeSheet->setCellValue('E'.$index, 'Company name');
$activeSheet->setCellValue('F'.$index, 'Industry');
$activeSheet->setCellValue('G'.$index, 'Job Position');
$activeSheet->setCellValue('H'.$index, 'Department');
$activeSheet->setCellValue('I'.$index, 'Hobbies');

$fill = $activeSheet->getStyle('A5:I5')->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('cc99ff');
$style = $activeSheet->getStyle('A3')->getFont();
$style->setSize(12);
$style->setBold(true);

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I');

foreach($cols as $item)
{
    if($item == 'B')
    {
        setBackgroundColor($activeSheet, $item, 5, 35);
    }
    else if($item == 'G')
    {
        setBackgroundColor($activeSheet, $item, 5, 40);
    }
	else if($item == 'I')
    {
        setBackgroundColor($activeSheet, $item, 5, 80);
    }
    else if($item == 'H' || $item == 'D')
    {
        setBackgroundColor($activeSheet, $item, 5, 28);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 5, 18);   
    }
}

// End init excel

/******** Query Data ********/  
$db = JFactory::getDbo();
$conditions = array();

//echo strval($_GET['i']);exit;
$conditions['listIndustry'] = strval($_GET['i']);
$conditions['department'] = strval($_GET['d']);
$conditions['listJob'] = strval($_GET['j']);
$limit = strval($_GET['limit']);
$data = reportProvider::memberstatisticjob($conditions,$db,$limit);

//echo "<pre>";print_r($data);exit;
if(!empty($data))
{
    $activeSheet->mergeCells('A3:H3');
    $activeSheet->setCellValue('A3', 'Total: '.$data['total'].'. Display top: '.count($data['items']));   
	$style = $activeSheet->getStyle('A3')->getFont();
    $style->setSize(16);
    $style->setBold(true);
    $style->setColor( new PHPExcel_Style_Color( 'CC0000' ) );
    
    /*********** BIND TO EXCEL ***********/
    $index = 6;
    $data = $data['items'];
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {
        
	   $hobbies = unserialize($data[$i]->hobbies);
	   $listHobbies = "";
	   if(!empty($hobbies)){
		 $sql = "SELECT title as hobbies FROM #__wrd_hobbies WHERE id IN (".implode(",",$hobbies).")";
		// echo $sql."<hr/>";
		 $db->setQuery($sql);
		 $hobbies_rows = $db->loadObjectList();
		 foreach($hobbies_rows as $k => $v){
			$listHobbies .= $v->hobbies.",";
		 }	
	   }
		//echo $listHobbies;
		$activeSheet->setCellValue('A'.$index, $data[$i]->member_id);
		$activeSheet->getStyle('A'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
        $activeSheet->setCellValue('B'.$index, $data[$i]->l_name.' '.$data[$i]->f_name);
        $activeSheet->setCellValueExplicit('C'.$index, $data[$i]->phone, PHPExcel_Cell_DataType::TYPE_STRING);     
        $activeSheet->getStyle('C'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $activeSheet->setCellValue('D'.$index, $data[$i]->p_email);                
        $activeSheet->setCellValue('E'.$index, $data[$i]->company_name);   
        $activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                 
        $activeSheet->setCellValue('F'.$index, $data[$i]->industry);            
        $activeSheet->setCellValue('G'.$index, $data[$i]->job_position);            
        $activeSheet->setCellValue('H'.$index, $data[$i]->department);
		$activeSheet->setCellValue('I'.$index, $listHobbies);	
        
		$index++;
    }
    unset($data);
}
//exit;
$fileName = 'member_statistic_job_'.date('m_d_Y_H_i_s');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15){
	
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
